﻿CREATE TRIGGER [tr_NoteType_Enum]
ON [dbo].[ts_NoteType]
FOR INSERT,UPDATE,DELETE
AS 

IF @@ROWCOUNT = 0 RETURN
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN
IF NOT UPDATE(Name) AND EXISTS(SELECT * FROM inserted) RETURN

DECLARE @SQL nvarchar(max)

IF UPDATE(Name) AND
	 EXISTS(SELECT * FROM inserted i LEFT OUTER JOIN deleted d ON i.ID = d.ID WHERE i.Name <> ISNULL(d.Name,''))
BEGIN
	DECLARE @Exists tinyint,@OldText nvarchar(max),@ON sysname
	SELECT @SQL = admin.MakeEnumObjectNameSQL(admin.MakeNoteTypeEnumObjectName(ID),
																						dbo.fn_TriggerTable(object_name(@@PROCID)),
																						ID,
																						DEFAULT),
				 @Exists = CASE WHEN dbo.HelpText(admin.MakeNoteTypeEnumObjectName(ID)) LIKE '% '+CONVERT(varchar,ID)+' %' THEN 1 ELSE 0 END,
				 @OldText = dbo.HelpText(admin.MakeNoteTypeEnumObjectName(ID))
	FROM inserted 
	PRINT 'Here'
	PRINT('Object Name:'+ISNULL(@ON,'ON')+'
Old Object:
'+ISNULL(@OldText,'OldText')+'
New Object:
'+ISNULL(@SQL,'SQL'))
	PRINT @Exists
	IF @Exists = 0 EXEC(@SQL)
	RETURN
END

IF EXISTS(SELECT * FROM inserted) RETURN

DECLARE @E TABLE(name sysname)
DECLARE @RelObject sysname

INSERT INTO @E SELECT E.Name FROM deleted CROSS APPLY admin.GetEnumsBySignature(dbo.fn_TriggerTable(object_name(@@PROCID)),ID)E

SELECT @RelObject = DR.object_name +' depends on '+D.object_name FROM @E E
CROSS APPLY admin.Dependencies(E.name)D
CROSS APPLY admin.Dependencies(E.name)DR
WHERE D.relative_id = DR.object_id

IF NOT @RelObject IS NULL
BEGIN
	RAISERROR('%s',16,1,@RelObject)
	ROLLBACK
	RETURN
END ELSE 

SELECT @SQL = ISNULL(@SQL+'
','')+'EXEC dbo._DropObject '''+E.name+''''
FROM deleted CROSS APPLY admin.GetEnumsBySignature(dbo.fn_TriggerTable(object_name(@@PROCID)),ID)E

PRINT @SQL
EXEC(@SQL)
